package ru.ssau.university.web.beans;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.faces.bean.ManagedBean;
import javax.faces.bean.SessionScoped;

import ru.ssau.university.persistence.dao.GroupDAO;
import ru.ssau.university.persistence.dao.SpecialityDAO;
import ru.ssau.university.persistence.dao.TeacherDAO;
import ru.ssau.university.persistence.entity.Group;
import ru.ssau.university.persistence.entity.Speciality;
import ru.ssau.university.persistence.entity.Teacher;
import ru.ssau.university.web.helper.ListWrapper;
import ru.ssau.university.web.helper.Report;

@ManagedBean(name = "reportsBean")
@SessionScoped
public class ReportsBean {
	private ListWrapper<Report> reports;
	private ListWrapper<Teacher> teachers;
	private ListWrapper<Speciality> specialities;
	private List<String> headers;
	private List<List<String>> rows;
	private boolean teachersMode = false;
	private boolean spMode = false;
		
	public ReportsBean() {
		
	}
	
	public String initAll() {
		initReports();
		initTeachers();
		initSpecialities();
		return null;
	}

	private void initSpecialities() {
		specialities = new ListWrapper<Speciality>();
		SpecialityDAO dao = new SpecialityDAO();
		specialities.setItems(dao.getAll());
	}

	private void initReports() {
		reports = new ListWrapper<Report>();
		List<Report> items = new ArrayList<Report>();
		items.add(new Report(1L, "Количество студентов по факультетам"));
		items.add(new Report(2L, "Количество предметов по специальностям"));
		items.add(new Report(3L, "Список отличников по факультетам"));
		items.add(new Report(4L, "Расписание для преподавателя"));
		items.add(new Report(5L, "Специальности, на которых преподает преподаватель"));
		items.add(new Report(6L, "Списки групп некоторой специальности"));
		items.add(new Report(7L, "Количество часов, закрепленных за кафедрой"));
		items.add(new Report(8L, "Количество часов для каждого преподавателя"));
		reports.setItems(items);
	}
	
	public void makeReport() {
		if (reports.getId() == 1L) {
			teachersMode = false;
			spMode = false;
			makeFirst();
		} else if (reports.getId() == 2L) {
			teachersMode = false;
			spMode = false;
			makeSecond();
		} else if (reports.getId() == 3L) {
			teachersMode = false;
			spMode = false;
			makeThird();
		} else if (reports.getId() == 8L) {
			teachersMode = false;
			spMode = false;
			makeEights();
		} else if (reports.getId() == 7L) {
			teachersMode = false;
			spMode = false;
			makeSeventh();
		} else if (reports.getId() == 4L) {
			teachersMode = true;
			spMode = false;
			if (teachers.getId() != null) {
				makeFourth();
			}
		} else if (reports.getId() == 5L) {
			teachersMode = true;
			spMode = false;
			if (teachers.getId() != null) {
				makeFifth();
			}
		} else if (reports.getId() == 6L) {
			teachersMode = false;
			spMode = true;
			if (specialities.getId() != null) {
				makeSixth();
			}
		}
	}

	private void makeSixth() {
		headers = new ArrayList<String>();
		headers.add("Группа");
		headers.add("ФИО студента");
		
		Connection con;
		ResultSet rs = null;
		try {
			con = DriverManager.getConnection("jdbc:mysql://localhost/university", "root", "1");
			PreparedStatement stmt = con.prepareStatement(
					"select g.ID as ID, s.FULL_NAME as FIO " +
					"from A_GROUP g, STUDENT s  " +
					"where g.SPECIALITY_ID = ? " +
					"and g.ID = s.A_GROUP_ID;");
			stmt.setLong(1, specialities.getId());
			rs = stmt.executeQuery();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		rows = new ArrayList<List<String>>();
		while (true) {
			try {
				if (rs.next()) {
					List<String> row  = new ArrayList<String>();
					Long groupId = rs.getLong("ID");
					GroupDAO dao = new GroupDAO();
					Group g = dao.findById(groupId);
					row.add(g.getTitle());
					row.add(rs.getString("FIO"));
					rows.add(row);
				} else {
					break;
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	private void makeFifth() {
		headers = new ArrayList<String>();
		headers.add("Специалность");
		
		Connection con;
		ResultSet rs = null;
		try {
			con = DriverManager.getConnection("jdbc:mysql://localhost/university", "root", "1");
			PreparedStatement stmt = con.prepareStatement("select distinct sp.TITLE as TITLE " +
					"from SPECIALITY sp, TEACHER_ACT ta, A_GROUP g " +
					"where sp.ID = g.SPECIALITY_ID " +
					"and   ta.A_GROUP_ID = g.id " +
					"and   ta.TEACHER_ID = ?;");
			stmt.setLong(1, teachers.getId());
			rs = stmt.executeQuery();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		rows = new ArrayList<List<String>>();
		while (true) {
			try {
				if (rs.next()) {
					List<String> row  = new ArrayList<String>();
					row.add(rs.getString("TITLE"));
					rows.add(row);
				} else {
					break;
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	private void makeFourth() {
		headers = new ArrayList<String>();
		headers.add("Неделя");
		headers.add("День недели");
		headers.add("Пара #");
		headers.add("Дисциплина");
		headers.add("Группа");
		
		Connection con;
		ResultSet rs = null;
		try {
			con = DriverManager.getConnection("jdbc:mysql://localhost/university", "root", "1");
			PreparedStatement stmt = con.prepareStatement(
					"select s.WEEK as WEEK, s.DAY_OF_WEEK as DAY, s.LESSON as LESSON, subj.TITLE as TITLE, g.ID as ID " +
					"from TEACHER_ACT ta, A_GROUP g, Schedule s, PLAN_ITEM p, Subject subj " +
					"where ta.TEACHER_ID = ? " +
					"and   g.ID = ta.A_GROUP_ID " +
					"and   p.ID = ta.PLAN_ITEM_ID " +
					"and   subj.ID = p.SUBJECT_ID " +
					"and   s.A_GROUP_ID = g.id;");
			stmt.setLong(1, teachers.getId());
			rs = stmt.executeQuery();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		rows = new ArrayList<List<String>>();
		while (true) {
			try {
				if (rs.next()) {
					List<String> row  = new ArrayList<String>();
					String w = rs.getString("WEEK");
					if ("0".equals(w)) {
						row.add("Четная");
					} else {
						row.add("Нечетная");
					}
					
					String d = rs.getString("DAY");
					if ("0".equals(d)) {
						row.add("Понедельник");
					} else if ("1".equals(d)) {
						row.add("Вторник");
					} else if ("2".equals(d)) {
						row.add("Среда");
					} else if ("3".equals(d)) {
						row.add("Четверг");
					} else if ("4".equals(d)) {
						row.add("Пятница");
					} else if ("5".equals(d)) {
						row.add("Суббота");
					}
					
					row.add(String.valueOf(rs.getInt("LESSON") + 1));
					row.add(rs.getString("TITLE"));
					
					Long groupId = rs.getLong("ID");
					GroupDAO dao = new GroupDAO();
					Group g = dao.findById(groupId);
					row.add(g.getTitle());
					
					rows.add(row);
				} else {
					break;
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	private void initTeachers() {
		teachers = new ListWrapper<Teacher>();
		TeacherDAO dao = new TeacherDAO();
		teachers.setItems(dao.getAll());
	}

	private void makeSeventh() {
		headers = new ArrayList<String>();
		headers.add("Кафедра");
		headers.add("Количество часов");
		
		rows = new ArrayList<List<String>>();
		ResultSet rs = executeQuery("select d.TITLE as TITLE, sum(p.hours) as HOURS " +
				"from TEACHER t, PLAN_ITEM p, TEACHER_ACT ta, DEPARTMENT d " +
				"where t.DEPARTMENT_ID = d.ID " +
				"and   ta.TEACHER_ID = t.ID " +
				"and   p.id = ta.PLAN_ITEM_ID;"
				);
		while (true) {
			try {
				if (rs.next()) {
					List<String> row  = new ArrayList<String>();
					row.add(rs.getString("TITLE"));
					row.add(rs.getString("HOURS"));
					rows.add(row);
				} else {
					break;
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	private void makeEights() {
		headers = new ArrayList<String>();
		headers.add("Преподаватель");
		headers.add("Количество часов");
		
		rows = new ArrayList<List<String>>();
		ResultSet rs = executeQuery("select t.NAME as NAME, sum(p.hours) as HOURS " +
				"from TEACHER t, PLAN_ITEM p, TEACHER_ACT ta " +
				"where ta.TEACHER_ID = t.ID " +
				"and   p.id = ta.PLAN_ITEM_ID " +
				"group by t.NAME;"
				);
		while (true) {
			try {
				if (rs.next()) {
					List<String> row  = new ArrayList<String>();
					row.add(rs.getString("NAME"));
					row.add(rs.getString("HOURS"));
					rows.add(row);
				} else {
					break;
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	private void makeThird() {
		headers = new ArrayList<String>();
		headers.add("Факультет");
		headers.add("ФИО студента");
		
		rows = new ArrayList<List<String>>();
		ResultSet rs = executeQuery("select f.TITLE as TITLE, s.FULL_NAME as NAME " +
				"from PROGRESS p, STUDENT s, A_GROUP g, SPECIALITY sp, FACULTY f " +
				"where p.STUDENT_ID = s.id " +
				"and   s.A_GROUP_ID = g.id " +
				"and   g.SPECIALITY_ID = sp.id " +
				"and   sp.FACULTY_ID = f.id " +
				"and   p.MARK < 5 " +
				"and not exists (" +
				"	select s2.id " +
				"   from PROGRESS p2, STUDENT s2 " +
				"   where p2.STUDENT_ID = s2.ID " +
				"   and   s2.id = s.id " +
				"   and   p2.MARK < 5);"
				);
		while (true) {
			try {
				if (rs.next()) {
					List<String> row  = new ArrayList<String>();
					row.add(rs.getString("TITLE"));
					row.add(rs.getString("NAME"));
					rows.add(row);
				} else {
					break;
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	private void makeSecond() {
		headers = new ArrayList<String>();
		headers.add("Специальность");
		headers.add("Количетсво предметов");
		
		rows = new ArrayList<List<String>>();
		ResultSet rs = executeQuery("select spec.TITLE as TITLE, count(subj.id) as C " +
				"from SUBJECT subj, SPECIALITY spec, PLAN_ITEM p " +
				"where subj.id = p.SUBJECT_ID " +
				"and   spec.id = p.SPECIALITY_ID " +
				"group by spec.TITLE;");
		while (true) {
			try {
				if (rs.next()) {
					List<String> row  = new ArrayList<String>();
					row.add(rs.getString("TITLE"));
					row.add(rs.getString("C"));
					rows.add(row);
				} else {
					break;
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	private void makeFirst() {
		headers = new ArrayList<String>();
		headers.add("Факультет");
		headers.add("Количетсво студентов");
		
		rows = new ArrayList<List<String>>();
		ResultSet rs = executeQuery("select f.TITLE as TITLE, count(s.ID) as C " +
					"from STUDENT s, A_GROUP g, SPECIALITY sp, FACULTY f " +
					"where s.A_GROUP_ID = g.id " +
					"and   g.SPECIALITY_ID = sp.id " +
					"and   sp.FACULTY_ID = f.id " +
					"group by f.TITLE;");
		while (true) {
			try {
				if (rs.next()) {
					List<String> row  = new ArrayList<String>();
					row.add(rs.getString("TITLE"));
					row.add(rs.getString("C"));
					rows.add(row);
				} else {
					break;
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	private ResultSet executeQuery(String query) {
		try {
			Connection con = DriverManager.getConnection("jdbc:mysql://localhost/university", "root", "1");
			PreparedStatement stmt = con.prepareStatement(query);
			ResultSet rs = stmt.executeQuery();
			return rs;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	public ListWrapper<Report> getReports() {
		return reports;
	}

	public void setReports(ListWrapper<Report> reports) {
		this.reports = reports;
	}

	public List<String> getHeaders() {
		return headers;
	}

	public void setHeaders(List<String> headers) {
		this.headers = headers;
	}

	public List<List<String>> getRows() {
		return rows;
	}

	public void setRows(List<List<String>> rows) {
		this.rows = rows;
	}

	public ListWrapper<Teacher> getTeachers() {
		return teachers;
	}

	public void setTeachers(ListWrapper<Teacher> teachers) {
		this.teachers = teachers;
	}

	public boolean isTeachersMode() {
		return teachersMode;
	}

	public void setTeachersMode(boolean teachersMode) {
		this.teachersMode = teachersMode;
	}

	public boolean isSpMode() {
		return spMode;
	}

	public void setSpMode(boolean spMode) {
		this.spMode = spMode;
	}

	public ListWrapper<Speciality> getSpecialities() {
		return specialities;
	}

	public void setSpecialities(ListWrapper<Speciality> specialities) {
		this.specialities = specialities;
	}
	
}
